# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql


# 医保信息
class MedicareDrugsPipeline(object):

    def __init__(self, settings):
        self.settings = settings

    @classmethod
    def from_crawler(cls, crawler):
        return cls(crawler.settings)

    def open_spider(self, spider):
        # 连接数据库
        self.connect = pymysql.connect(
            host=self.settings.get('MYSQL_HOST'),
            port=self.settings.get('MYSQL_PORT'),
            db=self.settings.get('MYSQL_DATABASE'),
            user=self.settings.get('MYSQL_USER'),
            passwd=self.settings.get('MYSQL_PASSWORD'),
            charset='utf8',
            use_unicode=True)

        # 通过cursor执行增删查改
        self.cursor = self.connect.cursor();
        self.connect.autocommit(True)

    def close_spider(self, spider):
        self.cursor.close()
        self.connect.close()

    def process_item(self, item, spider):
        try:
            goodscode = item['goodscode']
            registeredproductname = item['registeredproductname']
            goodsname = item['goodsname']
            registeredmedicinemodel = item['registeredmedicinemodel']
            registeredoutlook = item['registeredoutlook']
            materialname = item['materialname']
            factor = item['factor']
            minunit = item['minunit']
            unit = item['unit']
            companynamesc = item['companynamesc']
            approvalcode = item['approvalcode']
            goodsstandardcode = item['goodsstandardcode']
            productinsurancetype = item['productinsurancetype']
            productcode = item['productcode']
            productname = item['productname']
            productmedicinemodel = item['productmedicinemodel']
            productremark = item['productremark']

            sql = 'insert into medicaredrugs(goodscode,registeredproductname,goodsname,registeredmedicinemodel,registeredoutlook,materialname,factor,minunit,unit,companynamesc' \
                  ',approvalcode,goodsstandardcode,productinsurancetype,productcode,productname,productmedicinemodel,productremark)' \
                  ' values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
            self.cursor.execute(sql, (goodscode, registeredproductname, goodsname,
                                      registeredmedicinemodel, registeredoutlook, materialname,
                                      factor,
                                      minunit,
                                      unit,
                                      companynamesc,
                                      approvalcode,
                                      goodsstandardcode,
                                      productinsurancetype,
                                      productcode,
                                      productname,
                                      productmedicinemodel,
                                      productremark
                                      ))
            self.connect.commit()
            print("药品:" + str(goodsname) + str(goodscode) + "爬取成功！")
        except Exception as e:
            print("药品:" + str(goodsname) + str(goodscode) + "爬取错误！" + e)
            # print("Error '%s' happened on line %d" % (e[1], e[2].tb_lineno))
            pass
        return item
